# 异常值处理，在"售价（元）"的异常值处理要保证其值最多保留一位小数：
import numpy as np
import pandas as pd

brand_file_path = '../../data/raw data/餐饮连锁品牌数据.xlsx'
sheet_name = '菜品信息'
df_dish = pd.read_excel(brand_file_path, sheet_name)

print("\n【空值统计】")
print(df_dish.isnull().sum())
print("======================")

# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_dish.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")

# --- 删除空值 ---
before_rows = df_dish.shape[0]
df_dish.dropna(inplace=True)
after_rows = df_dish.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证空值 ---
print("\n【空值处理后验证】")
print(df_dish.isnull().sum().sum())
print('======================')
print('======================')

# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_dish.duplicated().sum())
print('================================')

if df_dish.duplicated().sum() > 0:
    print(df_dish[df_dish.duplicated()])

df_dish.drop_duplicates(inplace=True)

print("\n【重复值处理后验证】")
print(df_dish.duplicated().sum())
print('======================')
print('======================')

# ======================================
# 4️⃣ 识别并修正异常值
# ======================================
print("\n【数值列统计描述】")
print(df_dish.describe())

# -------------------------
# 菜品ID
# -------------------------
outer = '菜品ID'
if outer in df_dish.columns:
    # 去除非字符串类型ID
    invalid_ids = df_dish[~df_dish[outer].astype(str).str.startswith('DSH')]
    print(f"\n检测到异常菜品ID数量：{len(invalid_ids)}")
    df_dish.loc[~df_dish[outer].astype(str).str.startswith('DSH'), outer] = np.nan

# -------------------------
# 售价(元) - 优化后的异常值处理
# -------------------------
outer = '售价(元)'
if outer in df_dish.columns:
    mean_sales = df_dish[outer].mean()
    std_sales = df_dish[outer].std()
    upper = mean_sales + 2.5 * std_sales  # 调整为2.5倍标准差，更宽容
    lower = max(mean_sales - 2.5 * std_sales, 0)

    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常售价数量：{len(outliers)}")
    median_sales = df_dish[outer].median()

    # 先替换异常值为中位数，然后统一保留一位小数
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    # 确保所有售价数据最多保留一位小数
    df_dish[outer] = df_dish[outer].round(2)

    print(f"【异常值处理】已将 {len(outliers)} 个售价异常值替换为中位数 {median_sales:.1f}，并确保所有值保留一位小数。")

# -------------------------
# 成本(元)
# -------------------------
outer = '成本(元)'
if outer in df_dish.columns:
    mean_cost = df_dish[outer].mean()
    std_cost = df_dish[outer].std()
    upper = mean_cost + 2.8 * std_cost  # 成本波动略大，放宽到2.8倍标准差
    lower = max(mean_cost - 2.8 * std_cost, 0)
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常成本数量：{len(outliers)}")
    median_cost = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_cost
    print(f"【异常值处理】已将 {len(outliers)} 个成本异常值替换为中位数 {median_cost}。")

# -------------------------
# 制作时间(分钟)
# -------------------------
outer = '制作时间(分钟)'
if outer in df_dish.columns:
    upper = 120  # 认为超过2小时属于异常
    lower = 0  # 不存在负值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常制作时间数量：{len(outliers)}")
    median_time = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_time
    print(f"【异常值处理】已将 {len(outliers)} 个制作时间异常值替换为中位数 {median_time}。")

# -------------------------
# 是否核心菜品
# -------------------------
outer = '是否核心菜品'
if outer in df_dish.columns:
    invalid_core = df_dish[~df_dish[outer].isin(['是', '否'])]
    print(f"\n检测到异常核心菜品标记数量：{len(invalid_core)}")
    mode_core = df_dish[outer].mode()[0]
    df_dish.loc[~df_dish[outer].isin(['是', '否']), outer] = mode_core
    print(f"【异常值处理】已将 {len(invalid_core)} 个异常标记替换为众数 {mode_core}。")

print('======================================')
print('======================================')

# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '../../data/cleared data/餐饮连锁品牌数据_菜品信息4_cleaned.xlsx'
df_dish.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")